package com.prinxchengshan.reportbase.service;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.prinxchengshan.reportbase.Utils.DateUtil;
import com.prinxchengshan.reportbase.Utils.ExcelUtil;
import com.prinxchengshan.reportbase.Utils.JCoDestinationBuilder;
import com.prinxchengshan.reportbase.mapper.NewCreditInputHkMapper;
import com.prinxchengshan.reportbase.mapper.NewCrmSapHKMapper;
import com.prinxchengshan.reportbase.pojo.*;
import com.sap.conn.jco.*;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentException;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.function.Function;
import java.util.stream.Collectors;

@Service
public class NewCreditInputHkService {

    @Autowired
    private NewCreditInputHkMapper newCreditInputHkMapper;

    @Autowired
    private NewCrmSapHKMapper newCrmSapHKMapper;

    @Autowired
    private List<SapCustomerCredit> sapCustomerCreditList; // 从SAP中获取的列表数据


    @Autowired
    private NewCreditinputHk newCreditinputHk; // 从CRM中获取的数据

    @Autowired
    private NewCrmSapHk newCrmSapHk;// 待插入数据的行数据

    // 设置标志位，标志是不是第一次调用
    private boolean flag = false;

    // 上次调用日期
    private String lastDate;

    // 上次调用时间
    private String lastTime;

    // 从crm中获取的数据的map集合（CO号作为主键）
    private Map<String, NewCreditinputHk> creditinputMap;

    // 日期处理工具类
    DateUtil dateUtil = new DateUtil();


    // 查询所有crm所有的还款数据
    public ArrayList<NewCreditinputHk> getCrmCreditList() {
        return newCreditInputHkMapper.getCrmCreditList();
    }

    // 增量查询SAP还款数据并将sap和crm的数据进行比对，将差异数据插入到数据库中
    // @Scheduled(cron = "0 30 8  * * ?")
    public void ImportFromSAP() throws JCoException, ParseException, IOException {
        // 使用工厂模式构建构建器构建链接地址
        JCoDestination destination = JCoDestinationBuilder.build();
        JCoFunction function = destination.getRepository().getFunction("ZFII034");
        // 设置导入参数，没有可以不写
        if (flag == false) {
            function.getImportParameterList().setValue("ZDATE", "20231108");
            // function.getImportParameterList().setValue("ZDATE","20230701");
            function.getImportParameterList().setValue("ZTIME", "000000");
            function.getImportParameterList().setValue("ZTYPE", "2");
            flag = true;
        } else {
            function.getImportParameterList().setValue("ZDATE", lastDate);
            function.getImportParameterList().setValue("ZTIME", lastTime);
            function.getImportParameterList().setValue("ZTYPE", "2");
        }

        lastDate = dateUtil.getToday();
        lastTime = dateUtil.getTimeStr();


        try {
            System.out.println(function);
            JCoContext.begin(destination);
            function.execute(destination);
            System.out.println(function);
            // logger.warn(function.toXML());
        } catch (JCoException e) {
            e.printStackTrace();
        }

        // 获取导出参数内表
        JCoTable exp_tab = function.getExportParameterList().getTable("EXP_TAB");


        // 将字符串转换为XML的形式
        String toXML = exp_tab.toXML();
        // 将xml中的数据添加到list集合中去，方便等下导入数据
        getXmlAttribute(toXML);

        // 获取crm数据库里面的视图
        ArrayList<NewCreditinputHk> crmCreditList = getCrmCreditList();


        ArrayList<NewCreditinputHk> distinctLists = crmCreditList.stream()
                .collect(Collectors.collectingAndThen
                        (Collectors.toCollection(() ->
                                        new TreeSet<>(Comparator.comparing(t -> t.getCreditNo()))),
                                ArrayList::new
                        )
                );
        // 将获取的list集合转换为map集合
        creditinputMap = distinctLists.stream().collect(Collectors.toMap(NewCreditinputHk::getCreditNo, Function.identity()));
        BigDecimal temp;
        BigDecimal zero = new BigDecimal("0.0");
        Date date = new Date();
        //  每日差异的列表
        List<NewCrmsapreportbase2> noZeros = new ArrayList<NewCrmsapreportbase2>();
        for (SapCustomerCredit sapCustomerCredit : sapCustomerCreditList) {
            newCreditinputHk = creditinputMap.get(sapCustomerCredit.getZxyzh());
            if (sapCustomerCredit.getZxyzh() == null) {
                continue;
            }
            // 加入非空判断
            if (newCreditinputHk == null) {
                continue;
            }
            if ("58250713".equals(sapCustomerCredit.getKunnr())) {
                continue;
            }

            newCrmSapHk.setCredit_no(sapCustomerCredit.getZxyzh());// crm信用证号
            newCrmSapHk.setNew_accountcode(sapCustomerCredit.getKunnr());// 客户号
            newCrmSapHk.setNew_accountidname(sapCustomerCredit.getName1());// 客户名称
            newCrmSapHk.setNew_credittypeid(sapCustomerCredit.getZxylx());// 信用类型
            // SAP总金额 = sap总金额  + 信用占用金额
            newCrmSapHk.setSap_totalamount(sapCustomerCredit.getZdbje().add(sapCustomerCredit.getZzyje()));
            temp = newCreditinputHk.getTotalAmount() == null ? zero : newCreditinputHk.getTotalAmount();
            newCrmSapHk.setNew_totalamount(
                    temp
            );// CRM总金额
            newCrmSapHk.setDiffernece_total(
                    newCrmSapHk.getSap_totalamount().subtract(newCrmSapHk.getNew_totalamount())
            );// 总金额差异
            newCrmSapHk.setSap_unpaidAmount( sapCustomerCredit.getZfhwhje());// SAP发货未还款金额
            // 如果总金额为0 而且占用金额不为0，等于占用金额
            if(sapCustomerCredit.getZdbje().equals(zero) && !sapCustomerCredit.getZzyje().equals(zero)){
                newCrmSapHk.setSap_unpaidAmount(sapCustomerCredit.getZzyje());
            }
            temp = newCreditinputHk.getUnpaidAmount() == null ? zero : newCreditinputHk.getUnpaidAmount();
            newCrmSapHk.setNew_unpaidAmount(temp);// CRM发货未还金额
            newCrmSapHk.setDifference_unpaid(
                    newCrmSapHk.getSap_unpaidAmount().subtract(newCrmSapHk.getNew_unpaidAmount())
            );// 已用金额差异
//           // 余额或者总额不为0就会发送邮件
            newCrmSapHk.setInsert_time(lastTime);
            newCrmSapHk.setInsert_date(lastDate);
            newCrmSapHk.setIs_counted(newCreditinputHk.getIsCounted());
//          // 在还款差异表的数据根据信用号查找数据
            QueryWrapper<NewCrmSapHk> wrapper1 = new QueryWrapper<>();
            wrapper1.eq("credit_no", newCrmSapHk.getCredit_no());
            Long count = newCrmSapHKMapper.selectCount(wrapper1);
            if (count == 0) {
//             //  如果没有这个还款流水号的数据，则进行增加
                newCrmSapHKMapper.insert(newCrmSapHk);
            } else {
//                如果存在这条还款流水号，就会根据还款流水号更新数据
                newCrmSapHKMapper.updateById(newCrmSapHk);
            }

        }


    }


    /**
     * dom4j解析Xml
     *
     * @param xml
     */
    public void getXmlAttribute(String xml) throws ParseException {
        Document doc = null;
        try {
            // 将字符串转为XML
            doc = DocumentHelper.parseText(xml);
            // 获取根节点
            Element rootElt = doc.getRootElement();
            List<Element> items = rootElt.elements();
//           这是一个全局变量，清空一下数据，免得有历史数据遗留
            sapCustomerCreditList.clear();
            for (Element recordEle : items) {
                // 获取item节点


                SapCustomerCredit sapCustomerCredit = new SapCustomerCredit();

                // BUKRS
                Iterator BUKRS = recordEle.elementIterator("BUKRS");
                while (BUKRS.hasNext()) {
                    Element abkRecord = (Element) BUKRS.next();
                    // 获取ABK节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setBukrs(text);
                }

                // ZXYZH
                Iterator ZXYZH = recordEle.elementIterator("ZXYZH");
                while (ZXYZH.hasNext()) {
                    Element abkRecord = (Element) ZXYZH.next();
                    // 获取ZXYZH节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZxyzh(text);
                }

                // 获取ZHKLSH节点
                Iterator ZHKLSH = recordEle.elementIterator("ZHKLSH");
                while (ZHKLSH.hasNext()) {
                    Element abkRecord = (Element) ZHKLSH.next();
                    // 获取ZHKLSH节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZhklsh(text);
                }

                // ZHKDH
                Iterator ZHKDH = recordEle.elementIterator("ZHKDH");
                while (ZHKLSH.hasNext()) {
                    Element abkRecord = (Element) ZHKDH.next();
                    // 获取ZHKDH节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZhkdh(text);
                }

                // KUNNR
                Iterator KUNNR = recordEle.elementIterator("KUNNR");
                while (KUNNR.hasNext()) {
                    Element abkRecord = (Element) KUNNR.next();
                    // 获取KUNNR节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setKunnr(text);
                }

                // BU_SORT2
                Iterator BU_SORT2 = recordEle.elementIterator("BU_SORT2");
                while (KUNNR.hasNext()) {
                    Element abkRecord = (Element) BU_SORT2.next();
                    // 获取KUNNR节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setBu_sort2(text);
                }

                // NAME1
                Iterator NAME1 = recordEle.elementIterator("NAME1");
                while (NAME1.hasNext()) {
                    Element abkRecord = (Element) NAME1.next();
                    // 获取name1节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setName1(text);
                }
                // ZXYLX
                Iterator ZXYLX = recordEle.elementIterator("ZXYLX");
                while (ZXYLX.hasNext()) {
                    Element abkRecord = (Element) ZXYLX.next();
                    // 获取ZXYLX节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZxylx(text);
                }
                // XYNAME
                Iterator XYNAME = recordEle.elementIterator("XYNAME");
                while (XYNAME.hasNext()) {
                    Element abkRecord = (Element) XYNAME.next();
                    // 获取XYNAME节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setXyname(text);
                }
                // ZNAME1
                Iterator ZNAME1 = recordEle.elementIterator("ZNAME1");
                while (ZNAME1.hasNext()) {
                    Element abkRecord = (Element) ZNAME1.next();
                    // 获取ZNAME1节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZname1(text);
                }
                // ZDBJE
                Iterator ZDBJE = recordEle.elementIterator("ZDBJE");
                while (ZDBJE.hasNext()) {
                    Element abkRecord = (Element) ZDBJE.next();
                    // 获取ZNAME1节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZdbje(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                // ZFHJE
                Iterator ZFHJE = recordEle.elementIterator("ZFHJE");
                while (ZFHJE.hasNext()) {
                    Element abkRecord = (Element) ZFHJE.next();
                    // 获取ZNAME1节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZfhje(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                // ZLJJE
                Iterator ZLJJE = recordEle.elementIterator("ZLJJE");
                while (ZLJJE.hasNext()) {
                    Element abkRecord = (Element) ZLJJE.next();
                    // 获取ZNAME1节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZljje(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                // ZFHWH
                Iterator ZFHWH = recordEle.elementIterator("ZFHWH");
                while (ZFHWH.hasNext()) {
                    Element abkRecord = (Element) ZFHWH.next();
                    // 获取ZNAME1节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZfhwh(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                Iterator ZFHWHJE = recordEle.elementIterator("ZFHWHJE");
                while (ZFHWHJE.hasNext()) {
                    Element abkRecord = (Element) ZFHWHJE.next();
                    // 获取ZNAME1节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZfhwhje(BigDecimal.valueOf(Double.parseDouble(text)));
                }

                // ZHKJE
                Iterator ZHKJE = recordEle.elementIterator("ZHKJE");
                while (ZHKJE.hasNext()) {
                    Element abkRecord = (Element) ZHKJE.next();
                    // 获取ZNAME1节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZhkje(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                // ZTZJE
                Iterator ZTZJE = recordEle.elementIterator("ZTZJE");
                while (ZTZJE.hasNext()) {
                    Element abkRecord = (Element) ZTZJE.next();
                    // 获取ZNAME1节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZtzje(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                // ZZYJE
                Iterator ZZYJE = recordEle.elementIterator("ZZYJE");
                while (ZZYJE.hasNext()) {
                    Element abkRecord = (Element) ZZYJE.next();
                    // 获取ZKYZT节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZzyje(BigDecimal.valueOf(Double.parseDouble(text)));
                }
                // ZKYZT
                Iterator ZKYZT = recordEle.elementIterator("ZKYZT");
                while (ZKYZT.hasNext()) {
                    Element abkRecord = (Element) ZKYZT.next();
                    // 获取ZKYZT节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZkyzt(text);
                }

                // ZTNAME
                Iterator ZTNAME = recordEle.elementIterator("ZTNAME");
                while (ZTNAME.hasNext()) {
                    Element abkRecord = (Element) ZTNAME.next();
                    // 获取ZTNAME节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZtname(text);
                }
                // ZXYZQSR
                Iterator ZXYZQSR = recordEle.elementIterator("ZXYZQSR");
                while (ZXYZQSR.hasNext()) {
                    Element abkRecord = (Element) ZXYZQSR.next();
                    // 获取ZXYZQSR节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
                    Date date = formatter.parse(text);
                    sapCustomerCredit.setZxyzqsr(date);
                }
                // ZXYZDQI
                Iterator ZXYZDQI = recordEle.elementIterator("ZXYZDQI");
                while (ZXYZDQI.hasNext()) {
                    Element abkRecord = (Element) ZXYZDQI.next();
                    // 获取ZXYZDQI节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
                    Date date = formatter.parse(text);
                    sapCustomerCredit.setZxyzdqi(date);
                }
                // ZYHLSH
                Iterator ZYHLSH = recordEle.elementIterator("ZYHLSH");
                while (ZYHLSH.hasNext()) {
                    Element abkRecord = (Element) ZYHLSH.next();
                    // 获取ZYHLSH节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setZyhlsh(text);
                }
                // PURPOSE
                Iterator PURPOSE = recordEle.elementIterator("PURPOSE");
                while (PURPOSE.hasNext()) {
                    Element abkRecord = (Element) PURPOSE.next();
                    // 获取PURPOSE节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setPurpose(text);
                }
                // CHANGED_AT
                Iterator CHANGED_AT = recordEle.elementIterator("CHANGED_AT");
                while (CHANGED_AT.hasNext()) {
                    Element abkRecord = (Element) CHANGED_AT.next();
                    // 获取CHANGED_AT节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
                    Date date = formatter.parse(text);
                    sapCustomerCredit.setChanged_at(date);
                }
                // URID
                Iterator URID = recordEle.elementIterator("URID");
                while (URID.hasNext()) {
                    Element abkRecord = (Element) URID.next();
                    // 获取URID节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setUrid(text);
                }
                // DBURID
                Iterator DBURID = recordEle.elementIterator("DBURID");
                while (DBURID.hasNext()) {
                    Element abkRecord = (Element) DBURID.next();
                    // 获取DBURID节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setDburid(text);
                }
                // DYURID
                Iterator DYURID = recordEle.elementIterator("DYURID");
                while (DYURID.hasNext()) {
                    Element abkRecord = (Element) DYURID.next();
                    // 获取DYURID节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    sapCustomerCredit.setDyurid(text);
                }
                // ZGXRQ
                Iterator ZGXRQ = recordEle.elementIterator("ZGXRQ");
                while (ZGXRQ.hasNext()) {
                    Element abkRecord = (Element) ZGXRQ.next();
                    // 获取ZGXRQ节点下的所有节点
                    Iterator f = abkRecord.elementIterator();
                    String text = abkRecord.getText();
                    SimpleDateFormat formatter = new SimpleDateFormat("yyyyMMdd");
                    Date date = formatter.parse(text);
                    sapCustomerCredit.setZgxrq(date);
                }
                sapCustomerCreditList.add(sapCustomerCredit);
//                }
            }
        } catch (DocumentException e) {
            e.printStackTrace();
        }
    }



    /**
     * 生成excel表格
     * @param noZeros
     * @throws IOException
     */
    public void getXssf(List<NewCrmSapHk> noZeros) throws IOException {
        XSSFWorkbook xssfWorkbook=new XSSFWorkbook();
        XSSFSheet xssfSheet=xssfWorkbook.createSheet("SAP-CRM每日信用差异_还款");
        //注意，这种新增sheet的方式，如果你的excel原来在第一个sheet页面上有内容，会自动覆盖原来的内容,所以尽量使用空的EXCEL文档
        String[] biaoti=new String[]{"CRM信用证号","客户号","客户名称","SAP总金额","CRM总金额","总金额差异",
                "SAP发货未还金额","CRM发货未还金额","发货未还金额差异"};

        Row row=xssfSheet.createRow(0);
        for (int i=0;i< biaoti.length;i++){
            row.createCell(i).setCellValue(biaoti[i]);
        }

        int rowIndex = 1;
        for (NewCrmSapHk noZero : noZeros) {
            int colunIndex = 0;
            XSSFRow row1 = xssfSheet.createRow(rowIndex++);
            row1.createCell(colunIndex++).setCellValue(noZero.getCredit_no());
            row1.createCell(colunIndex++).setCellValue(noZero.getNew_accountcode());
            row1.createCell(colunIndex++).setCellValue(noZero.getNew_accountidname());
            row1.createCell(colunIndex++).setCellValue(noZero.getSap_totalamount().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getNew_totalamount().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getDiffernece_total().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getSap_unpaidAmount().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getNew_unpaidAmount().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getDifference_unpaid().doubleValue());
        }




        String local_path = System.getProperty("user.dir");
        //获取当前用户的当前工作目录
        String path = local_path+"\\src\\main\\resources\\SAP-CRM每日信用差异.xlsx";
        //添加上文件的路径，组成文件的绝对路径
        File file=new File(path);
        FileOutputStream fileOutputStream=new FileOutputStream(file);
        xssfWorkbook.write(fileOutputStream);
        //通过流的方式写入到文件中中
        fileOutputStream.close();
        //使用完成之后要记得关闭这个流
    }


    public List<NewCrmSapHk> getNoZeros(){
        //      查询总金额和未还款不为0的行数据
        QueryWrapper<NewCrmSapHk> wrapper = new QueryWrapper();
        wrapper.
                eq("is_counted","是").
                and(
                        i->i.ne("differnece_total",0)
                                .or()
                                .ne("difference_unpaid",0)
                )
               ;


        List<NewCrmSapHk> noZeros = newCrmSapHKMapper.selectList(wrapper);
        return noZeros;
    }

    public List<NewCrmSapHk> getNoZeros(String lastDate,String lastTime){
        //      查询三个差异金额不为0的行数据
        QueryWrapper<NewCrmSapHk> wrapper = new QueryWrapper();

        String tempTime = "";
        String tempDate = "";

        if (lastTime.equals("000000")){
            tempTime = "230000";
            tempDate = Integer.parseInt(lastDate)-1+"";
        }else {
            tempTime = Integer.parseInt(lastTime)-10000+"";
            tempDate = lastDate;
        }

        wrapper.eq("insert_time",tempTime).
                eq("insert_date",tempDate)
               .and(i->i.ne("differnece_total",0).or().ne("difference_unpaid",0));
        List<NewCrmSapHk> noZeros = newCrmSapHKMapper.selectList(wrapper);
        return noZeros;
    }


    public void  updateNoZeros( List<NewCrmSapHk> noZeros ){
        // 对于SAP重推给CRM的数据，发送之前要重新校验一下数据是否正确
        for (NewCrmSapHk noZero : noZeros) {
            //获取crm数据库里面的视图
            QueryWrapper<NewCreditinputHk> updateWrapper = new QueryWrapper<>();
            updateWrapper.isNotNull("CO").
                    eq("CO",noZero.getCredit_no());

            NewCreditinputHk newCreditinputHk = newCreditInputHkMapper.selectOne(updateWrapper);
            if (newCreditinputHk == null){
                continue;
            }
            noZero.setNew_totalamount(newCreditinputHk.getTotalAmount());
            noZero.setNew_unpaidAmount(newCreditinputHk.getUnpaidAmount());
            noZero.setDiffernece_total(
                    noZero.getSap_totalamount().subtract(noZero.getNew_totalamount())
            );//总金额差异
            noZero.setDifference_unpaid(
                    noZero.getSap_unpaidAmount().subtract(noZero.getNew_unpaidAmount())
            );//发货未还金额差异

            QueryWrapper<NewCrmSapHk> wrapper = new QueryWrapper<>();
            // 通过流水号更新
            wrapper.eq("credit_no",noZero.getCredit_no());
            // 更新数据
            newCrmSapHKMapper.update(noZero,wrapper);

        }

    }

    /**
     * 生成excel表格
     * @param noZeros
     * @throws IOException
     */
    public XSSFWorkbook writeExcel(List<NewCrmSapHk> noZeros,XSSFWorkbook xssfWorkbook) throws IOException {
        if (xssfWorkbook == null){
            xssfWorkbook=new XSSFWorkbook();
        }
        XSSFSheet xssfSheet=xssfWorkbook.createSheet("SAP-CRM每日信用差异_还款");
        //注意，这种新增sheet的方式，如果你的excel原来在第一个sheet页面上有内容，会自动覆盖原来的内容,所以尽量使用空的EXCEL文档
        String[] biaoti=new String[]{"信用证号","客户号","客户名称","SAP总金额","CRM总金额","总金额差异",
               "SAP发货未还金额","CRM发货未还金额","发货未还金额差异"};
        Row row=xssfSheet.createRow(0);
        for (int i=0;i< biaoti.length;i++){
            row.createCell(i).setCellValue(biaoti[i]);
        }

        int rowIndex = 1;
        for (NewCrmSapHk noZero : noZeros) {
            int colunIndex = 0;
            XSSFRow row1 = xssfSheet.createRow(rowIndex++);
            row1.createCell(colunIndex++).setCellValue(noZero.getCredit_no());
            row1.createCell(colunIndex++).setCellValue(noZero.getNew_accountcode());
            row1.createCell(colunIndex++).setCellValue(noZero.getNew_accountidname());
            row1.createCell(colunIndex++).setCellValue(noZero.getSap_totalamount().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getNew_totalamount().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getDiffernece_total().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getSap_unpaidAmount().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getNew_unpaidAmount().doubleValue());
            row1.createCell(colunIndex++).setCellValue(noZero.getDifference_unpaid().doubleValue());
        }

        return  xssfWorkbook ;

    }

}
